Author

Scott Stewart

Modified

January 17, 2024

1 Setup

First, we need to set up our environment. The code we need for loading packages can be expanded below.

Packages:

Code
# load primary packages
library(here) # for relative directories
library(showtext) # fonts
library(readxl) # read data from Excel
library(labelled) # column labelling
library(janitor) # 2-way tables; exploratory analysis
library(scales) # formatting percentages
library(patchwork) # combining plots
library(ggeasy) # using column labels in plots
library(gt) # nice HTML tables
library(tinytable) # nice HTML/PDF tables without dependencies
library(gtExtras) # rich HTML tables (added on to functionality of {gt}
library(dlookr) # creating data dictionaries
library(psych) # exploratory analysis; summary stats
library(tidyverse) # multifunctional collection of packages

# knitr options
# knitr::opts_chunk$set(out.width = 700, out.height = 500)

2 Introduction

I am a big fan of the Thomas Lady Terriers basketball team, and I am forever grateful to Wright Media and Newstalk KCLI for broadcasting Terrier and Lady Terrier sports on Terrier TV, allowing me to watch the games while living out of state.

I have recorded and maintained player and team statistics for each game of the current season, but now I want to be able to automate some of the aggregations and maybe create some customized plots and tables from these stats. Enter R.

2.1 Load Game Data

2.1.1 Raw Data

The raw data is in Excel and cannot be downloaded from this website. However, you can email me and I can send it to you.

Let’s load the data using the readxl package:

Code
# file path
path <- here("_input_data/Lady-Terriers_STATS_updated-15JAN2024.xlsx")

# sheet names
sheets <- excel_sheets(path = path)

# choose all but first two sheets and reverse their order
games <- rev(sheets[-c(1, 2)]) |> 
  map_df(
    ~ read_excel(
      path = path, 
      sheet = .x, 
      range = "A4:V25",
      col_types = "text"),
    .id = "game")

2.1.2 Scores by Quarter

Code
# setup
scores_by_qtr <- games |> 
  filter(is.na(FTA)) |> 
  drop_na("Name") |> 
  filter(Name != "SCORE BY QUARTER") |> 
  mutate(
    team = str_to_title(Name),
    game = as.numeric(game)) |> 
  select(
    game, team, 
    Q1_pts = "Fouls",
    Q2_pts = "2PA",
    Q3_pts = "2PM",
    Q4_pts = "PCT...6") |> 
  mutate(
    across(c(Q1_pts:Q4_pts), as.numeric))

# one row per quarter per team
scores_qtr_long <- scores_by_qtr |> 
  pivot_longer(
    cols = -c(1,2),
    names_to = "qtr",
    values_to = "points") |> 
  mutate(quarter = str_sub(qtr, start = 2, end = 2) |> as.numeric()) |> 
  select(1, 2, 5, 4)

# one row per game per team
scores_qtr_wide <- scores_by_qtr |> 
  select(
    game, team,
    q1 = Q1_pts,
    q2 = Q2_pts,
    q3 = Q3_pts,
    q4 = Q4_pts) |> 
  mutate(
    h1 = q1 + q2,
    h2 = q3 + q4,
    g = h1 + h2) |> 
  set_variable_labels(
    game = "Game Number",
    team = "Team Name",
    q1 = "1st Quarter Points",
    q2 = "2nd Quarter Points",
    q3 = "3rd Quarter Points",
    q4 = "4th Quarter Points",
    h1 = "1st Half Points",
    h2 = "2nd Half Points",
    g = "Total Points")
Code
qtr_wide_col_labs <- scores_qtr_wide
names(qtr_wide_col_labs) <- as.character(var_label(qtr_wide_col_labs))
tinytable::tt(qtr_wide_col_labs, theme = "striped")
Table 1: Scores by Quarter, Half and Game
tinytable_kn84y5pzz89uuexmj0gq
Game Number Team Name 1st Quarter Points 2nd Quarter Points 3rd Quarter Points 4th Quarter Points 1st Half Points 2nd Half Points Total Points
1 Thomas 7 13 21 11 20 32 52
1 Cordell 11 10 4 9 21 13 34
2 Thomas 23 17 18 15 40 33 73
2 Geary 8 12 10 19 20 29 49
3 Thomas 7 19 7 10 26 17 43
3 Arapaho 14 13 4 13 27 17 44
4 Thomas 9 14 10 6 23 16 39
4 Burns Flat-Dill City 0 4 3 4 4 7 11
5 Thomas 6 7 5 5 13 10 23
5 Hobart 6 12 8 7 18 15 33
6 Thomas 8 18 6 7 26 13 39
6 Mangum 10 10 14 13 20 27 47
7 Thomas 19 10 11 12 29 23 52
7 Calumet 2 5 1 16 7 17 24
8 Thomas 5 11 6 11 16 17 33
8 Canton 15 17 4 7 32 11 43
9 Thomas 10 13 10 8 23 18 41
9 Union City 8 9 8 8 17 16 33
10 Thomas 14 12 5 14 26 19 45
10 Ringwood 5 4 4 8 9 12 21
11 Thomas 8 6 11 7 14 18 32
11 Canton 16 9 4 9 25 13 38
12 Thomas 3 13 15 9 16 24 40
12 Okeene 11 6 6 15 17 21 38
13 Thomas 4 11 9 8 15 17 32
13 Lookeba-Sickles 22 15 19 19 37 38 75
14 Thomas 10 7 8 8 17 16 33
14 Okarche 8 15 7 17 23 24 47
15 Thomas 7 11 8 13 18 21 39
15 Gracemont 4 3 3 16 7 19 26
16 Thomas 11 6 7 9 17 16 33
16 Corn Bible 3 7 6 10 10 16 26

2.1.3 Opponent Game Stats

Code
opponent_stats <- games |> 
  drop_na(Name) |> 
  filter(Name != "THOMAS") |> 
  slice_tail(n = 3, by = game) |> 
  slice_head(n = 1, by = game) |> 
  select(-c(2, 7, 10, 13, 16, 21, 22, 23)) |> 
  mutate(
    game = as.numeric(game),
    # opponent = if_else(Name == "BFDC", "Burns Flat-Dill City", Name),
    opponent = str_to_title(Name),
    across(3:15, as.numeric), 
    .after = Name) |> 
  mutate(PF = Fouls, .after = TO) |> 
  select(-c(Name, Fouls)) |> 
  relocate(opponent, .after = game)

2.1.4 Player Game Stats

Code
player_stats <- games |> 
  drop_na(`#`) |>
  rename(
    number = `#`,
    name = Name,
    PF = Fouls) |> 
  mutate(
    number = as.numeric(number),
    game = as.numeric(game),
    across(4:23, as.numeric)) |> 
  select(-c(7, 10, 13, 16))

2.2 Load Game Information

This includes game types (home, road, or neutral), locations, opponents and dates:

Code
dates_prep <- rev(sheets[-c(1, 2)]) |> 
  map_df(
    ~ read_excel(
      path = path, 
      sheet = .x, 
      range = "A1:U1",
      col_names = FALSE,
      col_types = c(
        "date", "text", "text", "text", "text", "text", "text", "text",
        "text", "text", "text", "text", "text", "text", "text", "text",
        "text", "text", "text", "text", "text")),
    .id = "game")
dates <- dates_prep |> 
  mutate(
    game = as.numeric(game),
    date = as.Date(`...1`),
    location = str_sub(`...9`, start = 3L, end = -1L),
    opponent = `...21`,
    .keep = "none") |> 
  mutate(
    type = case_when(
      location == "Thomas" ~ "Home",
      str_detect(location, "\\*") ~ "Tournament",
      str_detect(location, "\\†") ~ "Tournament",
      str_detect(location, "\\‡") ~ "Tournament",
      str_detect(location, "\\⁰") ~ "Playoff",
      .default = "Away") |> 
      factor(levels = c("Home", "Away", "Tournament", "Playoff")) |> 
      fct_drop(),
    .after = date) |> 
  mutate(
    location = if_else(
      type == "Tournament",
      str_sub(location, start = 1L, end = -2L),
      location))

3 Summarize Data

3.1 Opponent Game Stats

Code
opp_scores_qtr_wide <- scores_qtr_wide |> 
  filter(team != "Thomas") |> 
  select(1, 3:6)

opp_summary <- dates |> 
  select(game, date, location, type) |> 
  left_join(opponent_stats, by = "game") |> 
  left_join(opp_scores_qtr_wide, by = "game") |> 
  mutate(
    team = opponent,
    PTS = FTM + (2 * `2PM`) + (3 * `3PM`),
    `2PT_PCT` = round(`2PM` / `2PA`, digits = 3),
    `3PT_PCT` = round(`3PM` / `3PA`, digits = 3),
    FT_PCT = round(FTM / FTA, digits = 3),
    REB = OREB + DREB) |> 
  select(-opponent) |> 
  select(
    game, date, location, type, team, 
    PTS, OREB, DREB, REB, AST, STL, BLK, TO, PF,
    `2PA`, `2PM`, `2PT_PCT`, `3PA`, `3PM`, `3PT_PCT`, FTA, FTM, FT_PCT,
    q1, q2, q3, q4)

3.2 Thomas Game Stats

Code
tt_scores_qtr_wide <- scores_qtr_wide |> 
  filter(team == "Thomas") |> 
  select(1, 3:6)

tt_dates <- dates |> 
  select(game, date, location, type)

tt_summary <- player_stats |> 
  select(-c(2:3, 17:19)) |> 
  summarise(
    across(PF:TO, sum),
    .by = game) |> 
  left_join(tt_scores_qtr_wide, by = "game") |> 
  right_join(tt_dates, by = "game") |> 
  mutate(
    team = "Thomas",
    PTS = FTM + (2 * `2PM`) + (3 * `3PM`),
    `2PT_PCT` = round(`2PM` / `2PA`, digits = 3),
    `3PT_PCT` = round(`3PM` / `3PA`, digits = 3),
    FT_PCT = round(FTM / FTA, digits = 3),
    REB = OREB + DREB) |> 
  select(
    game, date, location, type, team, 
    PTS, OREB, DREB, REB, AST, STL, BLK, TO, PF,
    `2PA`, `2PM`, `2PT_PCT`, `3PA`, `3PM`, `3PT_PCT`, FTA, FTM, FT_PCT,
    q1, q2, q3, q4)

3.3 Combined Game Stats

Code
game_summaries <- bind_rows(tt_summary, opp_summary)
Code
game_summary_tt <- game_summaries |> 
  arrange(game) |> 
  select(game, date, type, team, PTS:TO) 

tinytable::tt(game_summary_tt, theme = "striped")
Table 2: Game Stats by Team
tinytable_iswqx1j0pz4id9qmzkao
game date type team PTS OREB DREB REB AST STL BLK TO
1 2023-11-28 Away Thomas 52 8 29 37 14 1 6 19
1 2023-11-28 Away Cordell 34 5 16 21 9 0 2 9
2 2023-12-01 Away Thomas 73 13 24 37 12 12 6 14
2 2023-12-01 Away Geary 49 8 14 22 7 6 3 19
3 2023-12-05 Home Thomas 43 8 20 28 12 11 5 17
3 2023-12-05 Home Arapaho 44 13 13 26 8 10 3 14
4 2023-12-07 Tournament Thomas 39 7 21 28 7 12 2 16
4 2023-12-07 Tournament Burns Flat-Dill City 11 3 11 14 3 6 3 24
5 2023-12-08 Tournament Thomas 23 7 13 20 1 5 4 13
5 2023-12-08 Tournament Hobart 33 5 18 23 6 8 4 18
6 2023-12-09 Tournament Thomas 39 8 18 26 5 7 3 21
6 2023-12-09 Tournament Mangum 47 9 14 23 7 7 0 18
7 2023-12-12 Away Thomas 52 18 21 39 10 13 4 7
7 2023-12-12 Away Calumet 24 6 12 18 5 5 5 18
8 2023-12-14 Home Thomas 33 3 17 20 4 7 1 15
8 2023-12-14 Home Canton 43 6 15 21 7 7 0 8
9 2023-12-18 Home Thomas 41 3 17 20 6 10 0 17
9 2023-12-18 Home Union City 31 6 10 16 7 9 1 20
10 2024-01-04 Tournament Thomas 45 15 19 34 7 12 4 11
10 2024-01-04 Tournament Ringwood 21 3 15 18 4 4 0 24
11 2024-01-05 Tournament Thomas 32 6 12 18 4 4 0 9
11 2024-01-05 Tournament Canton 38 7 12 19 5 4 1 12
12 2024-01-06 Tournament Thomas 40 11 16 27 7 9 0 12
12 2024-01-06 Tournament Okeene 38 7 9 16 6 3 2 13
13 2024-01-09 Away Thomas 32 7 14 21 4 3 0 18
13 2024-01-09 Away Lookeba-Sickles 75 11 18 29 20 8 2 10
14 2024-01-12 Away Thomas 33 5 13 18 6 4 3 15
14 2024-01-12 Away Okarche 47 9 12 21 8 6 2 11
15 2024-01-12 Away Thomas 39 15 17 32 10 19 3 13
15 2024-01-12 Away Gracemont 26 4 25 29 5 6 1 27
16 2024-01-15 Away Thomas 33 8 13 21 7 10 8 14
16 2024-01-15 Away Corn Bible 26 8 12 20 7 7 1 18

3.4 Player Stats

Code
# one row per player per game
player_stats_detailed <- dates |> 
  select(-location) |> 
  left_join(player_stats, by = "game") |> 
  select(-PTS) |> 
  mutate(
    PTS = FTM + (2 * `2PM`) + (3 * `3PM`),
    `2PT_PCT` = round(`2PM` / `2PA`, digits = 3),
    `3PT_PCT` = round(`3PM` / `3PA`, digits = 3),
    FT_PCT = round(FTM / FTA, digits = 3),
    REB = OREB + DREB) |> 
  rename(`+/-` = `+ / -`) |> 
  select(
    game, date, type, opponent, num = number, name, 
    PTS, OREB, DREB, REB, AST, STL, BLK, TO, PF, MIN, `+/-`,
    `2PA`, `2PM`, `2PT_PCT`, `3PA`, `3PM`, `3PT_PCT`, FTA, FTM, FT_PCT)

# one row per player - SUMS
player_stats_sums <- player_stats_detailed |> 
  summarise(
    num = first(num),
    G = n(),
    across(
      c(PTS:`2PM`, `3PA`, `3PM`, FTA, FTM),
      sum),
    .by = name) |> 
  select(num, everything())

# one row per player - MEANS
player_stats_means <- player_stats_sums |> 
  mutate(
    across(
      c(4:20),
      ~ round(.x / G, digits = 1))) |> 
  mutate(
    `2PT_PCT` = round(`2PM` / `2PA`, digits = 3),
    `3PT_PCT` = round(`3PM` / `3PA`, digits = 3),
    FT_PCT = round(FTM / FTA, digits = 3),
    TS_PCT = round(PTS / (2 * (`2PA` + `3PA` + (0.44 * FTA))), digits = 3),
    `AST/TO` = round(AST / TO, digits = 2)
  )
Code
player_stats_tt <- player_stats_means |> 
  arrange(desc(MIN)) |> 
  select(1:4, 7:14) 

tinytable::tt(player_stats_tt, theme = "striped")
Table 3: Player Stats (averages per game)
tinytable_lj28jijgj8ubrcr7dnup
num name G PTS REB AST STL BLK TO PF MIN +/-
5 Jorgen Stewart 16 11.1 3.4 1.5 1.7 0.3 1.9 1.8 29.0 4.6
15 Channing Wright 16 9.6 3.1 1.5 2.8 0.1 3.1 2.4 27.6 4.2
24 Madi Mannering 10 8.9 8.5 1.0 1.2 2.8 2.2 2.2 27.3 4.3
23 Kate Mannering 13 4.2 3.5 1.3 0.5 0.4 1.7 1.6 19.8 1.9
11 Annmarie Cometsevah 16 3.2 3.3 0.8 1.4 0.1 2.1 1.9 18.8 1.6
20 Abbi Crowdis 9 2.6 3.1 1.8 0.3 0.3 2.7 1.1 17.8 7.4
21 Jozlun Fite 16 3.4 3.8 0.3 0.5 0.2 1.5 1.2 16.6 1.6
14 McCartney Lisle 16 1.5 1.1 0.3 0.6 0.0 1.4 1.1 13.9 -0.3
22 Jaelyn Koontz 15 1.0 1.5 0.0 0.2 0.0 0.1 1.0 8.8 0.1
12 McKenzie McGuire 6 0.8 0.8 0.3 0.2 0.0 0.3 0.0 5.7 0.2
42 Sadie Kliewer 4 0.0 0.2 0.0 0.0 0.0 0.0 0.0 3.2 -2.8
35 Chesney Hazel 3 0.7 1.0 0.0 0.0 0.3 0.0 0.0 3.0 -1.7

3.5 Advanced Stats

3.5.1 Definitions

Let’s calculate some more advanced player stats, but first we will need to calculate a few game-level team stats. Namely:

  • FGA - total field goals attempted (2PA + 3PA) by Thomas (TLT_FGA) or opponent (OPP_FGA)
  • FGM - total field goals made (2PM + 3PM) by Thomas (TLT_FGM) or opponent (OPP_FGM)
  • FTA - total free throws attempted by Thomas (TLT_FTA) or opponent (OPP_FTA)
  • 3PA - total 3-pointers attempted by Thomas (TLT_3PA) or opponent (OPP_3PA)
  • OREB - total offensive rebounds by Thomas (TLT_OREB) or their opponent (OPP_OREB)
  • REB - total rebounds for Thomas (TLT_REB) or their opponent (OPP_REB)
  • TO - total turnovers for Thomas (TLT_TO) or their opponent (OPP_TO)

The TLT and OPP prefixes are acronyms for “Thomas Lady Terriers” and “opponent”, respectively. In the formulas below, the TLT prefix is implied for any stats/metrics that don’t have a prefix displayed.

True Shooting Percentage (TS_PCT) - this metric incorporates shooting efficiency for free throws, 2-point shots and 3-point shots:

TS\_PCT=\frac{PTS}{2\big(FGA+(0.44 \times FTA)\big)} Estimated Possessions (POSS) - this estimates the number of posessions a team has in a game based on shots taken, offensive rebounds and turnovers:

POSS=0.5 \times \big(FGA+(0.475 \times FTA)-OREB+TO\big)

It should be noted that POSS is a team metric only. This metric, while useful as a measure of pace, is also used to calculate some of the player metrics described below.

Rebounding Percentage (REB_PCT) - measures a player’s total rebounding contribution to the game, and is adjusted for time spent on the court:

REB\_PCT=\frac{REB \times \frac{32}{MIN}}{TLT\_REB+OPP\_REB} If a player plays all 32 minutes of the game, this metric will provide the proportion of a player’s rebounds to the total game rebounds of both teams. For the average rebounder, this metric should be close to 0.1 since there is a 1 of 10 players on the court will get a given rebound.

Assist Ratio (AST_RT) - measures the percentage of teammate baskets a player assisted on, and is adjusted for time spent on the court:

AST\_RT=\frac{AST}{\bigg(\frac{MIN}{32}\times TLT\_FGM\bigg)-FGM}

Assist Percentage (AST_PCT) - measures the proportion of player assists per team possession, and is adjusted for time spent on the court:

AST\_PCT=\frac{AST}{\frac{MIN}{32}\times TLT\_POSS}

If a team plays at a fast pace, this metric adjusts for pace because it’s based on total team possessions. This metric penalizes a player when teammates miss shots or turn the ball over.

Steal Percentage (STL_PCT) - proportion of opponent’s possessions in which a player gets a steal, and is adjusted for time spent on the court:

STL\_PCT=\frac{STL}{\frac{MIN}{32}\times OPP\_POSS}

Block Percentage (BLK_PCT) - proportion of opponent’s possessions in which a player gets a block, and is adjusted for time spent on the court:

BLK\_PCT=\frac{BLK}{\frac{MIN}{32}\times OPP\_POSS}

Turnover Percentage (TOV_PCT) - measures the proportion of player turnovers per team possession, and is adjusted for time spent on the court:

TOV\_PCT=\frac{TO}{\frac{MIN}{32}\times TLT\_POSS}

Player Efficiency (EFF) - this is a (somewhat crude) measure of player efficiency, which is an adjusted difference of positive stats to negative stats:

  1. positive stats: points, rebounds, assists, steals and blocks
  2. negative stats: missed field goals, missed free throws and turnovers

The sum of positive stats minus the sum of negative stats is then adjusted for time spent on the court:

$$ = (++++ \ -(-)-(-)-)

$$